﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Koonsoft.HYCRM.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Koonsoft.HYCRM.DAL
{
    public class TasksDAL
    {
        private static readonly Database database = DatabaseFactory.CreateDatabase("KOONCRMDB");

        /// <summary>
        /// 在数据库中新增待办任务
        /// </summary>
        /// <param name="task"></param>
        /// <returns></returns>
        public bool AddNewTask(TaskInfo task)
        {
            const string sql = @"INSERT INTO [KOONCRM].[dbo].[Tasks]
               ([CompanySerialNumber]
               ,[ClientID]
               ,[ClientType]
               ,[AuditMonth]
               ,[AuditDay]
               ,[EmpolyeeID]
               ,[BranchID]
               ,[RegionID]
               ,[CompanyID]
               ,[EnglishName]
               ,[ChineseName]
               ,[Status])
                VALUES
               (@CompanySerialNumber,
               @ClientID, 
               @ClientType,
               @AuditMonth,
               @AuditDay,
               @EmpolyeeID,
               @BranchID,
               @RegionID,
               @CompanyID,
               @EnglishName,
               @ChineseName,
               @Status)";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@CompanySerialNumber", DbType.String, task.CompanySerialNumber);
                database.AddInParameter(cmd, "@ClientID", DbType.String, task.ClientID);
                database.AddInParameter(cmd, "@ClientType", DbType.Int32, task.ClientType);
                database.AddInParameter(cmd, "@AuditMonth", DbType.Int32, task.AuditMonth);
                database.AddInParameter(cmd, "@AuditDay", DbType.Int32, task.AuditDay);
                database.AddInParameter(cmd, "@EmpolyeeID", DbType.Int32, task.EmpolyeeID);
                database.AddInParameter(cmd, "@BranchID", DbType.Int32, task.BranchID);
                database.AddInParameter(cmd, "@RegionID", DbType.Int32, task.RegionID);
                database.AddInParameter(cmd, "@CompanyID", DbType.String, task.CompanyID);
                database.AddInParameter(cmd, "@EnglishName", DbType.String, task.EnglishName);
                database.AddInParameter(cmd, "@ChineseName", DbType.String, task.ChineseName);
                database.AddInParameter(cmd, "@Status", DbType.Int32, task.Status);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        /// <summary>
        /// 在数据库中新增待办任务
        /// </summary>
        /// <param name="companySerialNumber"></param>
        /// <returns></returns>
        public bool AddNewTask(string companySerialNumber)
        {
            const string procedure = "Task_CreateNewTask";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@CompanySerialNumber", DbType.String, companySerialNumber);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        /// <summary>
        /// 移除任务
        /// </summary>
        /// <param name="companySerialNumber"></param>
        /// <returns></returns>
        public bool RemoveTask(string companySerialNumber)
        {
            const string sql = "delete from tasks where companyserialnumber=@companyserialnumber";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@companyserialnumber", DbType.String, companySerialNumber);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }


        /// <summary>
        /// 根据任务编号获取正确的任务
        /// </summary>
        /// <param name="taskID"></param>
        /// <returns></returns>
        public TaskInfo GetTaskByTaskID(int taskID)
        {
            const string sql = "select * from tasks where taskid=@taskid";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@taskid", DbType.Int32, taskID);

                DataSet ds;

                ds = database.ExecuteDataSet(cmd);

                if (ds != null)
                {
                    DataRow dr = ds.Tables[0].Rows[0];

                    TaskInfo taskInfo = new TaskInfo();

                    taskInfo.BranchID = Convert.ToInt32(dr["branchID"]);
                    taskInfo.EmpolyeeID = Convert.ToInt32(dr["empolyeeID"]);
                    taskInfo.TaskID = taskID;

                    return taskInfo;
                }

                return null;

            }
        }

        /// <summary>
        /// 根据员工编号获取分行待办任务
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <param name="auditMonth">待办月份</param>
        /// <returns></returns>
        public DataTable GetBranchTasks(int empolyeeID,int auditMonth)
        {
            const string procedure = "Task_GetBranchManagerTasks";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);
                database.AddInParameter(cmd, "@ordermonth", DbType.Int32, auditMonth);

                DataSet ds = database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 根据员工编号获取某个月的待办任务
        /// </summary>
        /// <param name="branchID"></param>
        /// <param name="empolyeeID"></param>
        /// <param name="auditMonth"></param>
        /// <param name="auditYear"></param>
        /// <returns></returns>
        public DataTable GetEmpolyeeTasks(int branchID,int empolyeeID,int auditMonth,int auditYear)
        {
            const string procedure = "AnnualCheck_FillRecord_GetEmpolyeeTasks";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@month", DbType.Int32, auditMonth);
                database.AddInParameter(cmd, "@branchID", DbType.Int32, branchID);
                database.AddInParameter(cmd, "@year", DbType.Int32, auditYear);
                database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);

                DataSet ds = database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 根据分行编号获取某个月的待办任务
        /// </summary>
        /// <param name="branchID"></param>
        /// <param name="auditMonth"></param>
        /// <param name="auditYear"></param>
        /// <returns></returns>
        public DataTable GetBranchTasks(int branchID,int auditMonth,int auditYear)
        {
            const string procedure = "AnnualCheck_FillRecord_GetBranchTasks";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@month", DbType.Int32, auditMonth);
                database.AddInParameter(cmd, "@branchID", DbType.Int32, branchID);
                database.AddInParameter(cmd, "@year", DbType.Int32, auditYear);

                DataSet ds = database.ExecuteDataSet(cmd);

                return ds.Tables[0];
            }
        }
    }
}
